import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * @author huangzd
 */
@SuppressWarnings({ "rawtypes", "unchecked","deprecation"})
public class ExeclUtils {
	public static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("#0.00");
	public static final String DEFAULT_VAL = "0";

	public static <T> Map<String, Object> getExeclData2Map(InputStream inputStream, List<String> fieldList, Class clazz)
			throws Exception {
		Map map = new HashMap();
		StringBuilder errMsg = new StringBuilder();// 错误信息集合

		Workbook workbook = null;
		List<T> list = new ArrayList<T>();
		workbook = WorkbookFactory.create(inputStream);
		Sheet sheet = workbook.getSheetAt(0);

		// 数据
		// 行
		for (Row row : sheet) {
			if (row == null || row.getRowNum() == 0) {
				continue;
			}

			T t = (T) clazz.newInstance();
			// 列
			for (Cell cell : row) {
				if (cell == null) {
					continue;
				}

				try {
					Field field = clazz.getDeclaredField(fieldList.get(cell.getColumnIndex()));
					field.setAccessible(true);
					field.set(t, getData(cell, field.getType()));
				} catch (Exception e) {
					// e.printStackTrace();
					// System.err.println("第" + (row.getRowNum() + 1) + "行第" +
					// (cell.getColumnIndex() + 1) + "列数据格式不匹配");
					errMsg.append("第" + (row.getRowNum() + 1) + "行")
							.append("第" + (cell.getColumnIndex() + 1) + "列数据格式不匹配\n");
				}
			}
			list.add(t);
		}

		workbook.close();

		map.put("data", list);
		map.put("errMsg", errMsg.toString());
		return map;
	}

	public static <T> List<T> getExeclData2List(InputStream inputStream, List<String> fieldList, Class clazz)
			throws Exception {
		Workbook workbook = null;
		List<T> list = new ArrayList<T>();
		workbook = WorkbookFactory.create(inputStream);
		Sheet sheet = workbook.getSheetAt(0);

		// 数据
		// 行
		for (Row row : sheet) {
			if (row == null || row.getRowNum() == 0) {
				continue;
			}

			T t = (T) clazz.newInstance();
			// 列
			for (Cell cell : row) {
				if (cell == null) {
					continue;
				}

				try {
					Field field = clazz.getDeclaredField(fieldList.get(cell.getColumnIndex()));
					field.setAccessible(true);
					field.set(t, getData(cell, field.getType()));
				} catch (Exception e) {
					// e.printStackTrace();
					System.err.println("第" + (row.getRowNum() + 1) + "行第" + (cell.getColumnIndex() + 1) + "列数据格式不匹配");
				}
			}
			list.add(t);
		}

		workbook.close();
		return list;
	}

	private static Object getData(Cell cell, Class clazz) throws Exception {
		Object obj = null;

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:// 字符串
			obj = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:// 数字

			if (clazz.equals(int.class) || clazz.equals(Integer.class)) {// int/Integer格式
				obj = (int) cell.getNumericCellValue();
				break;
			} else if (clazz.equals(double.class) || clazz.equals(Double.class)) {// double/Double格式
				obj = cell.getNumericCellValue();
				break;
			} else if (clazz.equals(Float.class) || clazz.equals(float.class)) {// Float/float格式
				obj = (float) cell.getNumericCellValue();
				break;
			} else if (clazz.equals(BigDecimal.class)) {// BigDecimal格式
				cell.setCellType(Cell.CELL_TYPE_STRING);
				obj = new BigDecimal(cell.getStringCellValue());
				break;
			} else if (clazz.equals(String.class)) {// 字符串格式
				BigDecimal decimal = new BigDecimal(cell.getNumericCellValue());
				obj = decimal.toString();
				break;
			} else if (clazz.equals(Date.class)) {// 日期格式
				obj = DateUtil.getJavaDate(cell.getNumericCellValue());
				break;
			} else if (Enum.class.isAssignableFrom(clazz)) { // 枚举类型
				obj = clazz.getEnumConstants()[(int) cell.getNumericCellValue()];
				break;
			}
			break;
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_FORMULA:
			// obj = String.valueOf(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_BOOLEAN:// boolean型值
			obj = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_ERROR:
			// obj = String.valueOf(cell.getErrorCellValue());
			System.out.println("Cell.CELL_TYPE_ERROR:" + cell.getErrorCellValue());
			break;
		default:
			break;
		}

		return obj;
	}

	public static String formatDecimal(Object o) {
		if (o == null) {
			return DEFAULT_VAL;
		}
		return DECIMAL_FORMAT.format(o);
	}
	
	/**--------------------------------------下方为导出execl功能(需修改)--------------------------------------------**/
	
	private static int enumIndex = 0;

	/**
	 * @description 导出数据到execl
	 */
	public static <T> OutputStream exportData2Execl(List<T> list,List<String> fieldList,String[][] enumStr, String[] headers,
			ServletOutputStream servletOutputStream) {
		// 声明一个工作薄
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
		// 声明一个单子并命名
		HSSFSheet hssfSheet = hssfWorkbook.createSheet("data");
		hssfSheet.setDefaultColumnWidth(15);
		// 样式
		HSSFCellStyle style = hssfWorkbook.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = hssfWorkbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 创建表头
		HSSFRow row = hssfSheet.createRow(0);
		// 单元格
		createCell(row, style, headers);

		// 填充数据
		OutputStream out = null;
		try {
			setValue(list.iterator(),fieldList,enumStr, hssfSheet, row, hssfWorkbook);
//			out = new FileOutputStream("D://Desktop//1.xls");
			out = new BufferedOutputStream(servletOutputStream);
			hssfWorkbook.write(out);
			out.flush();
			out.close();
			hssfWorkbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return out;
	}

	/**
	 * @description 设置execl头
	 */
	private static void createCell(HSSFRow row, HSSFCellStyle style, String[] headers) {
		HSSFCell cell = null;
		for (int i = 0; i < headers.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(headers[i]);
			cell.setCellStyle(style);
		}
	}

	/**
	 * @description 设置值
	 */
	private static <T> void setValue(Iterator<T> it,List<String> fieldList,String[][] enumStr, HSSFSheet hssfSheet, HSSFRow row, HSSFWorkbook hssfWorkbook) throws Exception {

		int index = 0;
		while (it.hasNext()) {
			enumIndex = 0;//枚举数组重置
			index++;
			row = hssfSheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			for (short i = 0; i < fieldList.size(); i++) {
				Field field = null;
				try {
					field = t.getClass().getDeclaredField(fieldList.get(i));//从当前类查找相关字段
				} catch (Exception e) {
					//从当前类查找相关字段,报错后从父类查找
					field = t.getClass().getSuperclass().getDeclaredField(fieldList.get(i));
				}
				setSingleValue(hssfSheet, row, i, field, hssfWorkbook, t,enumStr);
			}
		}
	}

	
	private static <T> void setSingleValue(HSSFSheet hssfSheet, HSSFRow row, short cellNum,  Field field,
			HSSFWorkbook hssfWorkbook, T t,String[][] enumStr) throws Exception{
		HSSFCell cell = row.createCell(cellNum);
		cell.setCellStyle(getStyle(hssfWorkbook));
		
		String fieldName = field.getName();
		String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);//设置get方法
		Class clazz = t.getClass();
		Method method = clazz.getMethod(getMethodName, new Class[] {});
		Object value = method.invoke(t, new Object[] {});
		String textValue = null;
		
		//根据不同类型设置值
		if (value instanceof Boolean) {
			if (enumStr == null) {
				textValue =  (Boolean) value?"1":"0";
			} else {
				textValue = (Boolean) value?enumStr[enumIndex][1]:enumStr[enumIndex][0];
				enumIndex++;//枚举数组+1
			}
			
		} else if (value instanceof Date) {
			Date date = (Date) value;
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			textValue = sdf.format(date);
		} else if(value instanceof Enum){//枚举类型
			Object[] objects = value.getClass().getEnumConstants();
			for(int i=0;i<objects.length;i++){
				if (value.equals(objects[i])) {
					if (enumStr == null) {
						textValue = i+"";
						break;
					} else {
						textValue = enumStr[enumIndex][i];
						enumIndex++;//枚举数组+1
					}
				}
			}
		} else {
			// 其它数据类型都当作字符串简单处理
			if (value != null) {
				textValue = value.toString();
			}
		}

		// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
		if (textValue != null) {
			Pattern p = Pattern.compile("^//d+(//.//d+)?$");
			Matcher matcher = p.matcher(textValue);
			if (matcher.matches()) {
				// 是数字当作double处理
				cell.setCellValue(Double.parseDouble(textValue));
			} else {
				HSSFRichTextString richString = new HSSFRichTextString(textValue);
				hssfSheet.setColumnWidth(cellNum, (short) (35.7 * 180));
				cell.setCellValue(richString);
			}
		}

	}

	private static HSSFCellStyle getStyle(HSSFWorkbook hssfWorkbook) {
		HSSFCellStyle style = hssfWorkbook.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style;
	}
}
